const express = require("express")
const app = express()
const router = express.Router()
const mysql = require('mysql')
const axios = require('axios');
const fs = require('fs')
const path = require('path')
const multer = require('multer');

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root', //连接名
    password: '123456',
    database: 'root',//数据库
    multipleStatements: true, // 支持执行多条 sql 语句
});


// 启动mysql,并连接到数据库
connection.connect((err, arr) => {
    console.log("数据库连接成功");
});


app.use(express.static('uploads'))

// ajax post传数据的
app.use(express.json()) // for parsing application/json
app.use(express.urlencoded({ extended: true })) // for parsing application/x-www-form-urlencoded


// 解决跨域问题  (大致是不管请求什么路由是都设置什么东西)
app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "X-Requested-With");
    res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
    res.header("X-Powered-By",' 3.2.1')
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
    });



   
router.get('/index', function (req,res) {
    var sql = "select * from guoQinProject"
    connection.query(sql,function(arr,err){
     
        res.json(err)
    })

})


router.get('/cha', function (req,res) {
    var {id} = req.query;
    var sql = `select * from guoQinProject where id = ${id}`
    connection.query(sql,function(arr,err){
        
        res.json(err)
    })

})


// 设置上传的目录
var upload = multer({ dest: 'uploads/' })

router.post('/zhen',upload.single('img'),function (req,res) {
    
    let imgpath = ''
 
    var {shoppingName,price,stock,shelfTime} = req.body;
    let { originalname, filename } = req.file;
   
    let ext = originalname.substring(originalname.indexOf('.')); // .png
    // 把上传成功后的文件进行重命名
    let oldPath = path.join(__dirname, './', 'uploads', filename);
    let newPath = path.join(__dirname, './', 'uploads', filename) + ext;

    fs.renameSync(oldPath, newPath)
    imgpath = `http://127.0.0.1:5000/${filename}${ext}`

    var sql = `insert into guoQinProject (name,img,price,stock,shelfTime) values ('${shoppingName}','${imgpath}',${price},${stock},'${shelfTime}')`

    connection.query(sql,function(arr,err){
    
    })

})

router.post('/gai',upload.single('img'),function (req,res) {
    
    let imgpath = ''
 
    
    var sql =``;
    if(req.file == undefined){
        let {id,shoppingName,img,price,stock} = req.body;
        sql = `update guoQinProject set name='${shoppingName}' ,img='${img}' ,price=${price} ,stock=${stock} where id = ${id}`;
    }else{
        let { originalname, filename } = req.file;
        let {id,shoppingName,price,stock} = req.body;
        let ext = originalname.substring(originalname.indexOf('.')); // .png
    // 把上传成功后的文件进行重命名
    let oldPath = path.join(__dirname, './', 'uploads', filename);
    let newPath = path.join(__dirname, './', 'uploads', filename) + ext;

    fs.renameSync(oldPath, newPath)
    imgpath = `http://127.0.0.1:5000/${filename}${ext}`
    
     sql = `update guoQinProject set name='${shoppingName}' ,img='${imgpath}' ,price=${price} ,stock=${stock} where id = ${id}`;
    }
    

    connection.query(sql,function(arr,err){
      console.log(arr);
      console.log(err);
       
    })

})


router.get('/shan', function (req,res) {
    var {id} = req.query;
    var sql = `delete from guoQinProject where id = ${id};`
    connection.query(sql,function(arr,err){
        
    })

})

app.use('/',router)

app.listen(5000, () => {
    console.log("接口启动");
})
